-- Start of DDL Script for Package Body APPS.XXGR_EXP_PKG
-- Generated 01-May-2013 17:26:19 from APPS@OTE.UPGRUAT

-- Drop the old instance of XXGR_EXP_PKG
DROP PACKAGE apps.xxgr_exp_pkg
/

CREATE OR REPLACE 
PACKAGE apps.xxgr_exp_pkg
IS
  --------------------------------------------------------------------------------------
  -- Name        : XXGR_EXP_PKG                                                       --
  -- Type        : Package                                                            --
  -- Purpose     : Contains procesess for FNDLOADER                                   --
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --------------------------------------------------------------------------------------
  --
  --TYPES
  --
  TYPE virtual_table_type IS TABLE OF VARCHAR2(4000);

  TYPE trec_args IS RECORD
  (
    arg01  VARCHAR2(255),
    arg02  VARCHAR2(255),
    arg03  VARCHAR2(255),
    arg04  VARCHAR2(255),
    arg05  VARCHAR2(255),
    arg06  VARCHAR2(255),
    arg07  VARCHAR2(255),
    arg08  VARCHAR2(255),
    arg09  VARCHAR2(255),
    arg10  VARCHAR2(255)
  );

  --
  --GLOBAL VARIABLES
  --
  --g_debug_level: 0 = NO DEBUG MESSAGES ON LOG.
  --               1 = ONLY ERRORS ON LOG.
  --               2 = ERRORS AND MESSAGES ON LOG.
  --               3 = ERRORS MESSAGES AND API DEBUG MESSAGES ON LOG.
  g_debug_level            NUMBER := 10;

  g_package_name  CONSTANT VARCHAR2(30) := 'XXGR_EXP_PKG';
  c_success       CONSTANT VARCHAR2(1) := '0';
  c_warning       CONSTANT VARCHAR2(1) := '1';
  c_failed        CONSTANT VARCHAR2(1) := '2';

  c_error_nvl     CONSTANT NUMBER := -9999999999999;

  g_request_id             NUMBER := NVL(fnd_global.conc_request_id, -99);

  g_fold_name              xxgr_exp_folder.out_folder_name%TYPE;
  g_fold_path              xxgr_exp_folder.out_folder_path%TYPE;

  G_form_debug number:= 1;
  --
  --PUBLIC PROCEDURES / FUNCTIONS
  --

  FUNCTION fun_get_ddl(p_xml_str CLOB)
    RETURN xxgr_exp_tbl_type;

  FUNCTION fun_get_file_cmd(p_mode IN VARCHAR2, p_folder_id IN NUMBER, p_file_id IN NUMBER)
    RETURN VARCHAR2;

  FUNCTION fun_get_folder_name(p_val IN VARCHAR2)
    RETURN VARCHAR2;

  FUNCTION fun_get_lvl2_parent_type(p_type IN VARCHAR2)
    RETURN VARCHAR2;

  FUNCTION fun_get_insert_script(p_owner VARCHAR2, p_table_name VARCHAR2)
    RETURN xxgr_exp_tbl_type;

  FUNCTION fun_get_conc_out_file(p_request_id IN NUMBER)
    RETURN VARCHAR2;

  PROCEDURE proc_write_down_sh(
    x_error_message     OUT VARCHAR2,
    x_return_code       OUT VARCHAR2,
    p_folder_id      IN     NUMBER);

  PROCEDURE proc_write_up_sh(
    x_error_message     OUT VARCHAR2,
    x_return_code       OUT VARCHAR2,
    p_folder_id      IN     NUMBER);

  FUNCTION fun_search(
    p_type                   IN VARCHAR2,
    p_file_name              IN VARCHAR2 DEFAULT NULL,
    p_attribute1             IN VARCHAR2 DEFAULT NULL,
    p_last_updated_by        IN VARCHAR2 DEFAULT NULL,
    p_last_update_date_from  IN DATE DEFAULT NULL,
    p_last_update_date_to    IN DATE DEFAULT NULL)
    RETURN xxgr_exp_search_tbl
    PIPELINED
    PARALLEL_ENABLE;
END xxgr_exp_pkg;
/


CREATE OR REPLACE 
PACKAGE BODY apps.xxgr_exp_pkg
IS
  g_url  VARCHAR2(1000);

  --------------------------------------------------------------------------------------
  -- Name        : XXGR_EXP_PKG                                                       --
  -- Type        : Package                                                            --
  -- Purpose     : Contains procesess for FNDLOADER                                   --
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --------------------------------------------------------------------------------------

  --================================================================================--
  --   NAME     : log
  --   PURPOSE  : Log messages to log file and dbms_output.put_line('');
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  PROCEDURE LOG(p_message_text IN VARCHAR2 DEFAULT NULL)
  IS
  BEGIN
    fnd_file.put_line(fnd_file.LOG, NVL(p_message_text, ' '));
    DBMS_OUTPUT.put_line(NVL(p_message_text, ' '));
  END LOG;

  --================================================================================--
  --   NAME     : out
  --   PURPOSE  : Log messages to out file and dbms_output.put_line('');
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  PROCEDURE out(p_message_text IN VARCHAR2 DEFAULT NULL)
  IS
  BEGIN
    fnd_file.put_line(fnd_file.output, NVL(p_message_text, ' '));
    DBMS_OUTPUT.put_line(NVL(p_message_text, ' '));
  END out;

  --================================================================================--
  --   NAME     : log_message
  --   PURPOSE  : Log messages to table xxgr_debug_messages
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  PROCEDURE log_message(
    p_package_name   IN VARCHAR2,
    p_function_call  IN VARCHAR2,
    p_stage          IN VARCHAR2,
    p_message_text   IN VARCHAR2 DEFAULT NULL)
  IS
    l_package_name  VARCHAR2(60);
    l_message_text  VARCHAR2(3276);
  BEGIN
    IF (p_package_name IS NOT NULL) THEN
      l_package_name := p_package_name || '.';
    ELSE
      l_package_name := NULL;
    END IF;

    l_message_text := l_package_name || p_function_call || '@' || p_stage;

    IF (p_message_text IS NOT NULL) THEN
      l_message_text := l_message_text || ': ' || p_message_text;
    END IF;

    LOG(l_message_text);
  END log_message;

  --================================================================================--
  --   NAME     : fun_get_folder_name
  --   PURPOSE  : fun_get_folder_name
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    : Used in form XXGR_EXP_FOLDER.fmb
  --================================================================================--
  FUNCTION fun_get_folder_name(p_val IN VARCHAR2)
    RETURN VARCHAR2
  IS
    l_val  VARCHAR2(4000);
  BEGIN
    SELECT REPLACE(LOWER(REPLACE(p_val, '''')), ' ', '_') INTO l_val FROM DUAL;

    RETURN l_val;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN '-999';
  END fun_get_folder_name;

  --================================================================================--
  --   NAME     : fun_get_Lvl2_parent_type
  --   PURPOSE  : fun_get_Lvl2_parent_type
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  FUNCTION fun_get_lvl2_parent_type(p_type IN VARCHAR2)
    RETURN VARCHAR2
  IS
    l_val  VARCHAR2(4000);
  BEGIN
    SELECT TYPE
    INTO   l_val
    FROM   (SELECT     DISTINCT LEVEL,
                                TYPE,
                                type_desc,
                                parent_type
            FROM       xxgr_exp_type
            START WITH TYPE = p_type
            CONNECT BY TYPE = PRIOR parent_type)
    WHERE  parent_type = (SELECT TYPE
                          FROM   xxgr_exp_type
                          WHERE  parent_type IS NULL);

    RETURN l_val;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN '-999';
  END fun_get_lvl2_parent_type;

  --================================================================================--
  --   NAME     : fun_get_conc_out_file
  --   PURPOSE  : fun_get_conc_out_file
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  FUNCTION fun_get_conc_out_file(p_request_id IN NUMBER)
    RETURN VARCHAR2
  IS
    l_val  VARCHAR2(4000);
  BEGIN
    SELECT NVL(fout.file_name, req.outfile_name) out_file
    --, req.logfile_name log_file
    INTO   l_val
    FROM   fnd_concurrent_requests req, fnd_conc_req_outputs fout, fnd_mime_types_vl fmt
    WHERE  req.request_id = p_request_id
    AND    req.request_id = fout.concurrent_request_id(+)
    AND    fout.file_type = fmt.file_format_code(+);

    RETURN l_val;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN '-999';
  END fun_get_conc_out_file;

  --================================================================================--
  --   NAME     : fun_get_ddl
  --   PURPOSE  : fun_get_ddl
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  FUNCTION fun_get_ddl(p_xml_str CLOB)
    RETURN xxgr_exp_tbl_type
  IS
    l_lf_pos       NUMBER;
    l_prev_lf_pos  NUMBER;
    l_xml_str      CLOB;
    l_len          NUMBER;
    l_line         VARCHAR2(2000);
    l_data         xxgr_exp_tbl_type := xxgr_exp_tbl_type();
    i              NUMBER;
  BEGIN
    l_xml_str := p_xml_str;

    l_lf_pos := 0;
    l_prev_lf_pos := 0;

    LOOP
      --EXIT WHEN l_xml_str IS NULL;
      --I := NVL(i,0)+1;
      --EXIT WHEN I >= 1000;
      l_len := LENGTH(l_xml_str);
      DBMS_OUTPUT.put_line('l_len [' || l_len || ']');

      l_lf_pos :=
        DBMS_LOB.INSTR(
          l_xml_str,
          CHR(10),
          l_lf_pos + 1,
          1);
      --DBMS_OUTPUT.put_line(I||'.l_lf_pos ['||l_lf_pos||']');

      /*IF (NVL(l_lf_pos, 0) = 0) THEN
        l_lf_pos := 0;
      END IF;*/

      l_line := DBMS_LOB.SUBSTR(l_xml_str, l_lf_pos - l_prev_lf_pos, l_prev_lf_pos + 1);

      --DBMS_OUTPUT.put_line(I||'. ['||l_line||']');

      l_data.EXTEND;
      l_data(l_data.COUNT) := l_line;

      --l_xml_str := DBMS_LOB.SUBSTR(l_xml_str, l_len-l_lf_pos, l_lf_pos+1);
      l_prev_lf_pos := l_lf_pos;
      EXIT WHEN l_lf_pos = 0;
    END LOOP;

    --   l_data.EXTEND;
    --    l_data(l_data.COUNT) := '/';

    RETURN l_data;
  END fun_get_ddl;

  --================================================================================--
  --   NAME     : fun_get_insert_script
  --   PURPOSE  : fun_get_insert_script
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  FUNCTION fun_get_insert_script(p_owner VARCHAR2, p_table_name VARCHAR2)
    RETURN xxgr_exp_tbl_type
  IS
    v_column_list      VARCHAR2(32000);
    v_insert_list      VARCHAR2(32000);
    v_ref_cur_columns  VARCHAR2(32000);
    v_ref_cur_query    VARCHAR2(32000);
    v_ref_cur_output   VARCHAR2(32000);
    v_column_name      VARCHAR2(32000);

    CURSOR c1
    IS
      SELECT   column_name, data_type
      FROM     all_tab_columns
      WHERE    owner = p_owner AND table_name = p_table_name
      ORDER BY column_id;

    refcur             SYS_REFCURSOR;

    l_line             VARCHAR2(32000);
    l_data             xxgr_exp_tbl_type := xxgr_exp_tbl_type();
    i                  NUMBER;
  BEGIN
    FOR i IN c1 LOOP
      v_column_list := v_column_list || ',' || i.column_name;

      IF i.data_type = 'NUMBER' THEN
        v_column_name := i.column_name;
      ELSIF i.data_type = 'DATE' THEN
        v_column_name :=
             CHR(39)
          || 'to_date('
          || CHR(39)
          || '||chr(39)'
          || '||to_char('
          || i.column_name
          || ','
          || CHR(39)
          || 'dd/mm/yyyy hh:mi:ss'
          || CHR(39)
          || ')||chr(39)||'
          || CHR(39)
          || ', '
          || CHR(39)
          || '||chr(39)||'
          || CHR(39)
          || 'dd/mm/rrrr hh:mi:ss'
          || CHR(39)
          || '||chr(39)||'
          || CHR(39)
          || ')'
          || CHR(39);
      ELSIF i.data_type = 'VARCHAR2' THEN
        v_column_name := 'chr(39)||' || i.column_name || '||chr(39)';
      END IF;

      v_ref_cur_columns :=
        v_ref_cur_columns || '||' || CHR(39) || ',' || CHR(39) || '||' || v_column_name;
    END LOOP;

    v_column_list := LTRIM(v_column_list, ',');
    v_ref_cur_columns := SUBSTR(v_ref_cur_columns, 8);

    v_insert_list := 'INSERT INTO ' || p_table_name || ' (' || v_column_list || ') VALUES ';
    v_ref_cur_query :=
      'SELECT ' || v_ref_cur_columns || ' FROM ' || p_table_name || ' where rownum<10';

    IF v_ref_cur_columns IS NULL THEN
      l_line := 'Invalid owner: ' || p_owner || ' / table name: ' || p_table_name || ' combination!';

      l_data.EXTEND;
      l_data(l_data.COUNT) := l_line;
    ELSE
      OPEN refcur FOR v_ref_cur_query;

      LOOP
        FETCH refcur INTO v_ref_cur_output;

        EXIT WHEN refcur%NOTFOUND;
        v_ref_cur_output := '(' || v_ref_cur_output || ');';
        v_ref_cur_output := REPLACE(v_ref_cur_output, ',,', ',null,');
        v_ref_cur_output := REPLACE(v_ref_cur_output, '(,', '(null,');
        v_ref_cur_output := REPLACE(v_ref_cur_output, ',,)', ',null)');
        v_ref_cur_output := REPLACE(v_ref_cur_output, 'null,)', 'null,null)');
        v_ref_cur_output := v_insert_list || v_ref_cur_output;
        --DBMS_OUTPUT.put_line(v_ref_cur_output);

        l_line := v_ref_cur_output;

        l_data.EXTEND;
        l_data(l_data.COUNT) := l_line;
      END LOOP;
    END IF;

    RETURN l_data;
  END fun_get_insert_script;

  --================================================================================--
  --   NAME     : fun_get_file_cmd
  --   PURPOSE  : fun_get_file_cmd
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  FUNCTION fun_get_file_cmd(p_mode IN VARCHAR2, p_folder_id IN NUMBER, p_file_id IN NUMBER)
    RETURN VARCHAR2
  IS
    CURSOR c_file
    IS
      SELECT f.TYPE,
             f.file_name cmd_name,
             f.file_desc,
             f.attribute1 cmd_appl,
             f.attribute2 cmd_param1,
             f.attribute3 cmd_param2,
             fun_get_lvl2_parent_type(f.TYPE) lvl2,
             DECODE(SUBSTR(t.attribute2, 1, 1), '$', 'FNDLOAD', t.attribute2) cmd_type,
             t.attribute2 cmd_file,
             NVL(t.attribute3, t.TYPE) file_prefix,
             t.attribute4 cmd_cat_txt,
             t.attribute5 cmd_appl_txt,
             t.attribute6 cmd_name_txt,
             t.attribute7 cmd_param1_txt,
             t.attribute8 cmd_param2_txt
      FROM   xxgr_exp_file f, xxgr_exp_type t
      WHERE  f.TYPE = t.TYPE AND f.folder_id = p_folder_id AND f.file_id = p_file_id;

    my_cmd     VARCHAR2(4000);
    l_fnd_ext  VARCHAR2(10) := '.ldt';
    l_wf_ext   VARCHAR2(10) := '.wft';
    l_sql_ext  VARCHAR2(10) := '.sql';

    CURSOR c_lang
    IS
      SELECT   language_code
      FROM     fnd_languages
      WHERE    installed_flag IN ('B', 'I')
      ORDER BY installed_flag;
  BEGIN
    FOR file_rec IN c_file LOOP
      --***********************************************************************--
      -- FNDLOAD
      --***********************************************************************--
      IF file_rec.cmd_type = 'FNDLOAD' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               --             ' FNDLOAD apps/$pwd 0 Y DOWNLOAD '
               ' FNDLOAD $FCP_LOGIN 0 Y DOWNLOAD '
            || file_rec.cmd_file
            || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name;

          IF file_rec.cmd_param1 IS NULL THEN
            my_cmd := my_cmd || l_fnd_ext;
          ELSE
            my_cmd := my_cmd || '_' || file_rec.cmd_param1 || l_fnd_ext;
          END IF;

          my_cmd := my_cmd || ' ' || file_rec.cmd_cat_txt;

          IF file_rec.cmd_appl IS NOT NULL THEN
            my_cmd := my_cmd || ' ' || file_rec.cmd_appl_txt || '="' || file_rec.cmd_appl || '"';
          END IF;

          IF file_rec.cmd_name IS NOT NULL THEN
            my_cmd := my_cmd || ' ' || file_rec.cmd_name_txt || '="' || file_rec.cmd_name || '"';
          END IF;

          IF file_rec.cmd_param1 IS NOT NULL THEN
            my_cmd := my_cmd || ' ' || file_rec.cmd_param1_txt || '="' || file_rec.cmd_param1 || '"';
          END IF;

          IF file_rec.cmd_param2 IS NOT NULL THEN
            my_cmd := my_cmd || ' ' || file_rec.cmd_param2_txt || '="' || file_rec.cmd_param2 || '"';
          END IF;
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
               ' FNDLOAD apps/$pwd 0 Y UPLOAD '
            --   ' FNDLOAD $FCP_LOGIN 0 Y DOWNLOAD '
            || file_rec.cmd_file
            || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name;

          IF file_rec.cmd_param1 IS NULL THEN
            my_cmd := my_cmd || l_fnd_ext;
          ELSE
            my_cmd := my_cmd || '_' || file_rec.cmd_param1 || l_fnd_ext;
          END IF;
        END IF; --p_mode
      --***********************************************************************--
      -- WFLOAD
      --***********************************************************************--
      ELSIF file_rec.cmd_type = 'WFLOAD' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               --             ' WFLOAD apps/$pwd 0 Y DOWNLOAD '
               ' WFLOAD $FCP_LOGIN 0 Y DOWNLOAD '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || l_wf_ext;

          IF file_rec.cmd_name IS NOT NULL THEN
            my_cmd := my_cmd || ' ' || file_rec.cmd_name;
          END IF;
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
               ' WFLOAD apps/$pwd 0 Y DOWNLOAD ' --' WFLOAD $FCP_LOGIN 0 Y DOWNLOAD '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || l_wf_ext;
        END IF; --p_mode
      --***********************************************************************--
      -- FNDGFU
      --***********************************************************************--
      ELSIF file_rec.cmd_type = 'FNDGFU' THEN
        IF p_mode = 'DOWN' THEN
          --        my_cmd := ' FNDGFU apps/$pwd 0 Y DOWNLOAD=' || file_rec.cmd_name || ' LINE_BREAKS=CRLF ';
          my_cmd := ' FNDGFU $FCP_LOGIN 0 Y DOWNLOAD=' || file_rec.cmd_name || ' LINE_BREAKS=CRLF ';

          my_cmd :=
            my_cmd || file_rec.lvl2 || '/' || file_rec.file_prefix || '_' || file_rec.file_desc;
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
            ' FNDGFU apps/$pwd 0 Y PROGRAM_NAME="XXGR_EXP_PKG" PROGRAM_TAG="IMPORT" LANGUAGE="US" CONTENT_TYPE="text/plain" ';
          --my_cmd := ' FNDGFU $FCP_LOGIN 0 Y DOWNLOAD=' || file_rec.cmd_name || ' LINE_BREAKS=CRLF ';

          my_cmd :=
            my_cmd || file_rec.lvl2 || '/' || file_rec.file_prefix || '_' || file_rec.file_desc;
        --@         FNDGFU apps/apps 0 Y PROGRAM_NAME="XXGR_EXP_PKG" PROGRAM_TAG="IMPORT" LANGUAGE="US" CONTENT_TYPE="text/plain" "PO_Report.rtf'

        END IF; --p_mode
      --***********************************************************************--
      -- sqlplus
      --***********************************************************************--
      ELSIF file_rec.cmd_type = 'sqlplus' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               --             ' sqlplus -S apps/$pwd <<EOF >'
               ' sqlplus -S $FCP_LOGIN <<EOF >'
            --          || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || l_sql_ext
            || '
  set echo off
  set flush off
  set serveroutput off
  set termout off
  set newpage 0
  set space 0
  set pagesize 0
  set feedback off
  set heading off
  set trimspool on
  set tab off
  set linesize 5000
  set long 1000000
  set verify off
  set define off
  EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, ''STORAGE'', FALSE);
  EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, ''TABLESPACE'', FALSE);
  EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, ''SQLTERMINATOR'', TRUE);
  select dbms_metadata.get_ddl ('''
            || file_rec.cmd_cat_txt
            || ''', '''
            || file_rec.cmd_name
            || ''', '''
            || file_rec.cmd_appl
            || ''') from dual;
  EXIT;
EOF';
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
               ' sqlplus -S apps/$pwd <<EOF @'
            --   ' sqlplus -S $FCP_LOGIN <<EOF >'
            --          || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || l_sql_ext
            || '
  EXIT;
EOF';
        END IF; --p_mode
      --***********************************************************************--
      -- sqlplus8
      --***********************************************************************--
      ELSIF file_rec.cmd_type = 'sqlplus8' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               --             ' sqlplus -S apps/$pwd <<EOF >'
               ' sqlplus -S $FCP_LOGIN <<EOF >'
            --          || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || l_sql_ext
            || '
  set echo off
  set flush off
  set serveroutput off
  set termout off
  set newpage 0
  set space 0
  set pagesize 0
  set feedback off
  set heading off
  set trimspool on
  set tab off
  set linesize 5000
  set long 1000000
  set verify off
  set define off
  EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, ''STORAGE'', FALSE);
  EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, ''TABLESPACE'', FALSE);
  EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, ''SQLTERMINATOR'', TRUE);
select * from table(xxgr_exp_pkg.fun_get_ddl( DBMS_METADATA.get_ddl('''
            || file_rec.cmd_cat_txt
            || ''', '''
            || UPPER(file_rec.cmd_name)
            || ''', '''
            || UPPER(file_rec.cmd_appl)
            || ''') ));
  EXIT;
EOF';
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
               ' sqlplus -S apps/$pwd <<EOF @'
            --   ' sqlplus -S $FCP_LOGIN <<EOF >'
            --          || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || l_sql_ext
            || '
  EXIT;
EOF';
        END IF; --p_mode
      --***********************************************************************--
      -- sqlins
      --***********************************************************************--
      ELSIF file_rec.cmd_type = 'sqlins' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               --             ' sqlplus -S apps/$pwd <<EOF >'
               ' sqlplus -S $FCP_LOGIN <<EOF >'
            --          || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || l_sql_ext
            || '
  set echo off
  set flush off
  set serveroutput off
  set termout off
  set newpage 0
  set space 0
  set pagesize 0
  set feedback off
  set heading off
  set trimspool on
  set tab off
  set linesize 5000
  set long 1000000
  set verify off
  set define off
select * from table(xxgr_exp_pkg.fun_get_insert_script('''
            || UPPER(file_rec.cmd_name)
            || ''', '''
            || UPPER(file_rec.cmd_appl)
            || ''' ));
  EXIT;
EOF';
        --select * from table(xxgr_exp_pkg.fun_get_insert_script('XXGR', 'XXGR_EXP_FOLDER'));
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
               ' sqlplus -S apps/$pwd <<EOF @'
            --   ' sqlplus -S $FCP_LOGIN <<EOF >'
            --          || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || l_sql_ext
            || '
  EXIT;
EOF';
        END IF; --p_mode
      --***********************************************************************--
      -- cp / FMB
      --***********************************************************************--
      ELSIF file_rec.cmd_type LIKE 'cp%' AND file_rec.TYPE LIKE 'FMB%' THEN
        FOR lang_rec IN c_lang LOOP
          IF p_mode = 'DOWN' THEN
            my_cmd :=
                 my_cmd
              || '
               cp $AU_TOP/forms/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt
              || ' '
              || file_rec.lvl2
              || '/'
              || file_rec.file_prefix
              || '_'
              || lang_rec.language_code
              || '_'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt;
          ELSIF file_rec.cmd_type = 'cp' AND p_mode = 'UP' THEN
            my_cmd :=
                 my_cmd
              || '
               cp '
              || file_rec.lvl2
              || '/'
              || file_rec.file_prefix
              || '_'
              || lang_rec.language_code
              || '_'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt
              || ' $AU_TOP/forms/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt;

            my_cmd :=
                 my_cmd
              || '
              f60gen userid=apps/$pwd module=$AU_TOP/forms/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt
              || ' output_file=$'
              || file_rec.cmd_appl
              || '/forms/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || '.fmx';
          ELSIF file_rec.cmd_type = 'cp12' AND p_mode = 'UP' THEN
            my_cmd :=
                 my_cmd
              || '
               cp '
              || file_rec.lvl2
              || '/'
              || file_rec.file_prefix
              || '_'
              || lang_rec.language_code
              || '_'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt
              || ' $AU_TOP/forms/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt;

            my_cmd :=
                 my_cmd
              || '
              frmcmp_batch userid=apps/$pwd module=$AU_TOP/forms/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt
              || ' module_type=FORM output_file=$'
              || file_rec.cmd_appl
              || '/forms/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || '.fmx';
          END IF; --p_mode
        END LOOP;
      --***********************************************************************--
      -- cp / RDF
      --***********************************************************************--
      ELSIF file_rec.cmd_type = 'cp' AND file_rec.TYPE = 'RDF' THEN
        FOR lang_rec IN c_lang LOOP
          IF p_mode = 'DOWN' THEN
            my_cmd :=
                 my_cmd
              || '
              cp $'
              || file_rec.cmd_appl
              || '_TOP/reports/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt
              || ' '
              || file_rec.lvl2
              || '/'
              || file_rec.file_prefix
              || '_'
              || lang_rec.language_code
              || '_'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt;
          ELSIF p_mode = 'UP' THEN
            my_cmd :=
                 my_cmd
              || '
               cp '
              || file_rec.lvl2
              || '/'
              || file_rec.file_prefix
              || '_'
              || lang_rec.language_code
              || '_'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt
              || ' $'
              || file_rec.cmd_appl
              || '/reports/'
              || lang_rec.language_code
              || '/'
              || file_rec.cmd_name
              || file_rec.cmd_cat_txt;
          END IF; --p_mode
        END LOOP;
      --***********************************************************************--
      -- cp / PLL
      --***********************************************************************--
      ELSIF file_rec.cmd_type LIKE 'cp%' AND file_rec.TYPE LIKE 'PLL%' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               'cp $AU_TOP/resource/'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt
            || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt;
        ELSIF file_rec.cmd_type = 'cp' AND p_mode = 'UP' THEN
          my_cmd :=
               my_cmd
            || '
               cp '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt
            || ' $AU_TOP/resource/'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt;

          my_cmd :=
               my_cmd
            || '
              f60gen userid=apps/$pwd module_type=library module=$AU_TOP/resource/'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt
            || ' output_file=$AU_TOP/resource/'
            || file_rec.cmd_name
            || '.plx';
        ELSIF file_rec.cmd_type = 'cp12' AND p_mode = 'UP' THEN
          my_cmd :=
               my_cmd
            || '
               cp '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt
            || ' $AU_TOP/resource/'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt;

          my_cmd :=
               my_cmd
            || '
              frmcmp_batch.sh userid=apps/$pwd module_type=library module=$AU_TOP/resource/'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt
            || ' output_file=$AU_TOP/resource/'
            || file_rec.cmd_name
            || '.plx';
        END IF; --p_mode
      --***********************************************************************--
      -- cp / GIF
      --***********************************************************************--
      ELSIF file_rec.cmd_type = 'cp' AND file_rec.TYPE = 'GIF' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               'cp $OA_MEDIA/'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt
            || ' '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt;
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
               'cp '
            || file_rec.lvl2
            || '/'
            || file_rec.file_prefix
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt
            || ' $OA_MEDIA/'
            || file_rec.cmd_name
            || file_rec.cmd_cat_txt;
        END IF; --p_mode
      --***********************************************************************--
      -- java / !=XLIFF
      --***********************************************************************--
      ELSIF file_rec.cmd_type LIKE 'java%' AND file_rec.TYPE <> 'XLIFF' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               file_rec.cmd_type
            || ' \
DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $pwd \
-JDBC_CONNECTION $dbserver:$dbport:$dbsid'
            /*          || fnd_profile.VALUE('GL_JAHE_DB_HOST')
                      || ':'
                      || fnd_profile.VALUE('CSF_MAP_DB_PORT')
                      || ':'
                      || fnd_profile.VALUE('CSF_MAP_DB_SID')*/
            || ' \
-LOB_TYPE '
            || file_rec.cmd_param1
            || ' \
-APPS_SHORT_NAME '
            || file_rec.cmd_appl
            || ' \
-LOB_CODE '
            || file_rec.cmd_name
            || ' \
-LANGUAGE '
            || file_rec.cmd_param2
            || ' \
-XDO_FILE_TYPE '
            || file_rec.cmd_cat_txt
            || ' \
-NLS_LANG AMERICAN_AMERICA.EL8ISO8859P7

rm *'
            || file_rec.cmd_name_txt
            || ' '
            || file_rec.cmd_cat_txt
            || '_'
            || file_rec.cmd_param2
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_name_txt
            || '
mv '
            || file_rec.cmd_cat_txt
            || '_'
            || file_rec.cmd_param2
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_name_txt
            || ' '
            || file_rec.lvl2
            || '/';
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
               file_rec.cmd_type
            || ' \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $pwd \
-JDBC_CONNECTION $dbserver:$dbport:$dbsid'
            /*          || fnd_profile.VALUE('GL_JAHE_DB_HOST')
                      || ':'
                      || fnd_profile.VALUE('CSF_MAP_DB_PORT')
                      || ':'
                      || fnd_profile.VALUE('CSF_MAP_DB_SID')*/
            || ' \
-LOB_TYPE '
            || file_rec.cmd_param1
            || ' \
-APPS_SHORT_NAME '
            || file_rec.cmd_appl
            || ' \
-LOB_CODE '
            || file_rec.cmd_name
            || ' \
-LANGUAGE '
            || file_rec.cmd_param2
            || ' \
-XDO_FILE_TYPE '
            || file_rec.cmd_cat_txt
            || ' \
-NLS_LANG AMERICAN_AMERICA.EL8ISO8859P7'
            || ' \
-FILE_NAME '
            || file_rec.lvl2
            || '/'
            || file_rec.cmd_cat_txt
            || '_'
            || file_rec.cmd_param2
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_name_txt;
        END IF; --p_mode
      --***********************************************************************--
      -- java / XLIFF
      --***********************************************************************--
      ELSIF file_rec.cmd_type LIKE 'java%' AND file_rec.TYPE = 'XLIFF' THEN
        IF p_mode = 'DOWN' THEN
          my_cmd :=
               file_rec.cmd_type
            || ' \
DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $pwd \
-JDBC_CONNECTION $dbserver:$dbport:$dbsid'
            /*          || fnd_profile.VALUE('GL_JAHE_DB_HOST')
                      || ':'
                      || fnd_profile.VALUE('CSF_MAP_DB_PORT')
                      || ':'
                      || fnd_profile.VALUE('CSF_MAP_DB_SID')*/
            || ' \
-APPS_SHORT_NAME '
            || file_rec.cmd_appl
            || ' \
-TEMPLATE_CODE '
            || file_rec.cmd_name
            || '

rm *'
            || file_rec.cmd_name_txt
            || ' '
            || file_rec.cmd_cat_txt
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_name_txt
            || '
mv '
            || file_rec.cmd_cat_txt
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_name_txt
            || ' '
            || file_rec.lvl2
            || '/';
        ELSIF p_mode = 'UP' THEN
          my_cmd :=
               file_rec.cmd_type
            || ' \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $pwd \
-JDBC_CONNECTION $dbserver:$dbport:$dbsid'
            /*          || fnd_profile.VALUE('GL_JAHE_DB_HOST')
                      || ':'
                      || fnd_profile.VALUE('CSF_MAP_DB_PORT')
                      || ':'
                      || fnd_profile.VALUE('CSF_MAP_DB_SID')*/
            || ' \
-APPS_SHORT_NAME '
            || file_rec.cmd_appl
            || ' \
-FILE_NAME '
            || file_rec.lvl2
            || '/'
            || file_rec.cmd_cat_txt
            || '_'
            || file_rec.cmd_name
            || file_rec.cmd_name_txt;
        END IF; --p_mode
      END IF;
    END LOOP;

    RETURN my_cmd;
  END fun_get_file_cmd;

  --================================================================================--
  --   NAME     : proc_write_down_sh
  --   PURPOSE  : proc_write_down_sh
  --
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    : Concurrent XXGR_EXP_FOLDER_DOWN_SH
  --================================================================================--
  PROCEDURE proc_write_down_sh(
    x_error_message     OUT VARCHAR2,
    x_return_code       OUT VARCHAR2,
    p_folder_id      IN     NUMBER)
  IS
    c_procedure_name  CONSTANT VARCHAR2(30) := 'proc_write_down_sh';
    l_stage                    VARCHAR2(100);

    l_error_message            VARCHAR2(32767);
    l_return_code              VARCHAR2(100);

    exc_prc_down               EXCEPTION;

    l_request_id               NUMBER := 0;

    l_descr                    xxgr_exp_folder.folder_desc%TYPE;
    --    l_fold_name                xxgr_exp_folder.out_folder_name%TYPE;
    --    l_fold_path                xxgr_exp_folder.out_folder_path%TYPE;
    l_ext                      VARCHAR2(10) := '.ldt';

    CURSOR c_all_l2_types
    IS
      SELECT   t.TYPE
      FROM     xxgr_exp_type t
      WHERE    parent_type = (SELECT TYPE
                              FROM   xxgr_exp_type
                              WHERE  parent_type IS NULL)
      AND      EXISTS
                 (SELECT 1
                  FROM   xxgr_exp_file f
                  WHERE  f.folder_id = p_folder_id AND fun_get_lvl2_parent_type(f.TYPE) = t.TYPE)
      ORDER BY type_order;

    CURSOR c_all_types(p_lvl2 IN VARCHAR2)
    IS
      SELECT   f.TYPE,
               f.txt_path,
               t.attribute2 cmd,
               NVL(t.attribute3, t.TYPE) prefix
      FROM     (SELECT   DISTINCT f.TYPE TYPE,
                                  (SELECT     SYS_CONNECT_BY_PATH(type_desc, ': ') PATH
                                   FROM       xxgr_exp_type
                                   WHERE      TYPE = f.TYPE
                                   CONNECT BY PRIOR TYPE = parent_type
                                   START WITH parent_type IS NULL)
                                    txt_path
                FROM     xxgr_exp_file f
                WHERE    f.folder_id = p_folder_id
                ORDER BY f.TYPE) f,
               xxgr_exp_type t
      WHERE    f.TYPE = t.TYPE AND xxgr_exp_pkg.fun_get_lvl2_parent_type(f.TYPE) = p_lvl2
      ORDER BY t.type_order;

    CURSOR c_all_files(p_type IN VARCHAR2)
    IS
      SELECT   file_id,
               file_name,
               file_desc,
               attribute1 appl,
               attribute2 attr2
      FROM     xxgr_exp_file
      WHERE    folder_id = p_folder_id AND TYPE = p_type
      ORDER BY file_order, file_name;

    CURSOR c_req_set_files
    IS
      SELECT z.request_id,
             xxgr_exp_pkg.fun_get_conc_out_file(z.request_id) out_file,
             DECODE(z.rn, 1, 'IMP', 'DOC') fold,
             DECODE(
               rn,
               1, 'import.sh',
               UPPER(   xxgr_exp_pkg.fun_get_folder_name(user_concurrent_program_name)
                     || '.'
                     || NVL(
                          (SELECT fout.file_type
                           FROM   fnd_conc_req_outputs fout
                           WHERE  fout.concurrent_request_id = z.request_id),
                          'txt')))
               new_file
      FROM   (SELECT   request_id, RANK() OVER (ORDER BY request_id DESC) rn
              FROM     fnd_concurrent_requests r
              WHERE    priority_request_id = (SELECT p.priority_request_id
                                              FROM   fnd_concurrent_requests p
                                              WHERE  p.request_id = g_request_id)
              AND      has_sub_request = 'N'
              AND      request_type = 'P'
              AND      r.phase_code = 'C'
              AND      r.status_code = 'C'
              AND      request_id < g_request_id
              ORDER BY request_id) z,
             fnd_conc_req_summary_v c
      WHERE  z.request_id = c.request_id;

    my_cmd                     VARCHAR2(4000);
  BEGIN
    EXECUTE IMMEDIATE 'alter session set NLS_NUMERIC_CHARACTERS=''.,''';

    EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT = ''dd/mm/yyyy''';

    l_stage := 'START';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    --Parameters
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_folder_id [' || p_folder_id || ']');

    -- 1 *********************************************************************--
    l_stage := '#1 Write Header';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    SELECT folder_desc, out_folder_name, out_folder_path
    INTO   l_descr, g_fold_name, g_fold_path
    FROM   xxgr_exp_folder
    WHERE  folder_id = p_folder_id;

    out('# ==================================================================');
    out('# Generated by FNDLOADER');
    out('# Date: ' || TO_CHAR(SYSDATE, 'monthdd,yyyy hh24:mi:ss'));
    out('# User: ' || fnd_global.user_name);
    out('#');
    --    out('# Usage: sh ' || g_fold_name || '.sh apps_password');
    out('# Usage: sh ' || g_fold_name || '.sh');
    out('#');
    out('# ==================================================================');
    out('# File : ' || g_fold_name || '.sh');
    out('# Location: ' || g_fold_path);
    out('# Description: ' || l_descr);
    out('# ==================================================================');
    out('#');

    out('#********* Getting the apps pwd ****************');
    out('pwd=`sqlplus -s $FCP_LOGIN<< EOF ');
    out('  set echo off');
    out('  set flush off');
    out('  set serveroutput off');
    out('  set termout off');
    out('  set newpage 0');
    out('  set space 0');
    out('  set pagesize 0');
    out('  set feedback off');
    out('  set heading off');
    out('  set trimspool on');
    out('  set tab off');
    out('  set linesize 5000');
    out('  set long 1000000');
    out('  set verify off');
    out('  set define off');
    out('select substr(''$FCP_LOGIN'',instr(''$FCP_LOGIN'',''/'')+1)');
    out('from dual;');
    out('EXIT;');
    out('EOF`');
    out('');
    out('echo ''pwd:''$pwd    ');
    out('    ');

    out();
    out('#==  Create main folder directory ==');
    out(' if [ ! -d "' || g_fold_name || '" ];then');
    out(' mkdir "' || g_fold_name || '"');
    out(' fi');
    out(' cd ' || g_fold_name);
    out();

    -- to create only the needed subfolders
    FOR l2_types_rec IN c_all_l2_types LOOP
      out(' if [ ! -d "' || l2_types_rec.TYPE || '" ]; then');
      out('  mkdir ' || l2_types_rec.TYPE || ' ');
      out(' fi ');
      out();
    END LOOP;

    out('  if [ "x"${AME_TOP} = "x" ];then ');
    out('  AME_TOP=$PER_TOP ');
    out(' fi');
    out(' if [ ! -d "LOGS_DOWN" ]; then');
    out('  mkdir LOGS_DOWN ');
    out(' fi ');
    out();
    out(' if [ ! -d "LOGS_UP" ]; then');
    out('  mkdir LOGS_UP ');
    out(' fi ');
    out();
    out(' if [ ! -d "DOC" ]; then');
    out('  mkdir DOC ');
    out(' fi ');
    out();
    out(' if [ ! -d "IMP" ]; then');
    out('  mkdir IMP ');
    out(' fi ');
    out();
    out();
    /*    out(' ARGV="$@"');
        out();
        out(' pwd=$1');
        out();
        out(' if [ "x"$ARGV = "x" ] ; then');
        out('  echo "Error: Missing parameters!! "');
        out('  echo Enter apps password!');
        out('  stty -echo');
        out('  read apps_pwd');
        out('  pwd=$apps_pwd');
        out('  stty echo');
        out('  #echo "usage:  sh script_name apps_passwd"');
        out(' fi');
        out();*/
    out();
    out(' dbserver=' || fnd_profile.VALUE('GL_JAHE_DB_HOST'));
    out(' dbport=' || fnd_profile.VALUE('CSF_MAP_DB_PORT'));
    out(' dbsid=' || fnd_profile.VALUE('CSF_MAP_DB_SID'));
    out();
    out(' # Check Oracle connection. Is user/$pwd valid?');
    --    out(' exit | sqlplus -s apps/$pwd | grep denied > /dev/null');
    out(' exit | sqlplus -s $FCP_LOGIN | grep denied > /dev/null');

    out();
    out(' if [ $? -eq 1 ];then');
    out();
    out();

    FOR l2_types_rec IN c_all_l2_types LOOP
      out();
      out('##');
      out('## =================  ### ' || l2_types_rec.TYPE || ' ### ================= ##');
      out('##');
      out();

      FOR all_types_rec IN c_all_types(l2_types_rec.TYPE) LOOP
        out('## ------------- ' || all_types_rec.txt_path || ' ------------------');
        out();

        FOR all_files_rec IN c_all_files(all_types_rec.TYPE) LOOP
          my_cmd := fun_get_file_cmd('DOWN', p_folder_id, all_files_rec.file_id);

          out(my_cmd);
        END LOOP;

        out();
      END LOOP;
    END LOOP;

    out(' mv *.log LOGS_DOWN/');
    out(' cat LOGS_DOWN/* > LOGS_DOWN/ALL_LOGS_DOWN.log');
    out();
    out();

    out();
    out('##');
    out('## =================  ### Copy Request Sets Outputs ### ================= ##');
    out('##');
    out();

    FOR req_set_files IN c_req_set_files LOOP
      out(
           ' cp '
        || req_set_files.out_file
        || ' '
        || req_set_files.fold
        || '/'
        || req_set_files.new_file);
    END LOOP;

    out(' else');
    out(' echo "Invalid username/password! Try again. "');
    out(' fi');
  END proc_write_down_sh;

  --================================================================================--
  --   NAME     : proc_write_up_sh
  --   PURPOSE  : proc_write_up_sh
  --
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    : Concurrent XXGR_EXP_FOLDER_DOWN_SH
  --================================================================================--
  PROCEDURE proc_write_up_sh(
    x_error_message     OUT VARCHAR2,
    x_return_code       OUT VARCHAR2,
    p_folder_id      IN     NUMBER)
  IS
    c_procedure_name  CONSTANT VARCHAR2(30) := 'proc_write_up_sh';
    l_stage                    VARCHAR2(100);

    l_error_message            VARCHAR2(32767);
    l_return_code              VARCHAR2(100);

    exc_prc_down               EXCEPTION;

    l_request_id               NUMBER := 0;

    l_descr                    xxgr_exp_folder.folder_desc%TYPE;
    --    l_fold_name                xxgr_exp_folder.out_folder_name%TYPE;
    --    l_fold_path                xxgr_exp_folder.out_folder_path%TYPE;
    l_ext                      VARCHAR2(10) := '.ldt';

    CURSOR c_all_l2_types
    IS
      SELECT   t.TYPE
      FROM     xxgr_exp_type t
      WHERE    parent_type = (SELECT TYPE
                              FROM   xxgr_exp_type
                              WHERE  parent_type IS NULL)
      AND      EXISTS
                 (SELECT 1
                  FROM   xxgr_exp_file f
                  WHERE  f.folder_id = p_folder_id AND fun_get_lvl2_parent_type(f.TYPE) = t.TYPE)
      ORDER BY type_order;

    CURSOR c_all_types(p_lvl2 IN VARCHAR2)
    IS
      SELECT   f.TYPE,
               f.txt_path,
               t.attribute2 cmd,
               NVL(t.attribute3, t.TYPE) prefix
      FROM     (SELECT   DISTINCT f.TYPE TYPE,
                                  (SELECT     SYS_CONNECT_BY_PATH(type_desc, ': ') PATH
                                   FROM       xxgr_exp_type
                                   WHERE      TYPE = f.TYPE
                                   CONNECT BY PRIOR TYPE = parent_type
                                   START WITH parent_type IS NULL)
                                    txt_path
                FROM     xxgr_exp_file f
                WHERE    f.folder_id = p_folder_id
                ORDER BY f.TYPE) f,
               xxgr_exp_type t
      WHERE    f.TYPE = t.TYPE AND xxgr_exp_pkg.fun_get_lvl2_parent_type(f.TYPE) = p_lvl2
      ORDER BY t.type_order;

    CURSOR c_all_files(p_type IN VARCHAR2)
    IS
      SELECT   file_id,
               file_name,
               file_desc,
               attribute1 appl,
               attribute2 attr2
      FROM     xxgr_exp_file
      WHERE    folder_id = p_folder_id AND TYPE = p_type
      ORDER BY file_order, file_name;

    my_cmd                     VARCHAR2(4000);
    l_uplo_server              VARCHAR2(100);

    l_uplo_port                VARCHAR2(100);

    l_uplo_sid                 VARCHAR2(100);
  BEGIN
    EXECUTE IMMEDIATE 'alter session set NLS_NUMERIC_CHARACTERS=''.,''';

    EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT = ''dd/mm/yyyy''';

    l_stage := 'START';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    --Parameters
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_folder_id [' || p_folder_id || ']');

    -- 1 *********************************************************************--
    l_stage := '#1 Write Header';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    SELECT folder_desc, out_folder_name, out_folder_path /*,
                                         uplo_server,
                                         uplo_port,
                                         uplo_sid*/
    INTO   l_descr, g_fold_name, g_fold_path /*,
                                 l_uplo_server,
                                 l_uplo_port,
                                 l_uplo_sid*/
    FROM   xxgr_exp_folder
    WHERE  folder_id = p_folder_id;

    out('# ==================================================================');
    out('# Generated by FNDLOADER');
    out('# Date: ' || TO_CHAR(SYSDATE, 'monthdd,yyyy hh24:mi:ss'));
    out('# User: ' || fnd_global.user_name);
    out('#');
    out('# Usage: sh ' || g_fold_name || '.sh apps_password dbserver dbport dbsid');
    --    out('# Usage: sh ' || g_fold_name || '.sh');
    out('#');
    out('# ==================================================================');
    out('# File : ' || g_fold_name || '.sh');
    out('# Location: ' || g_fold_path);
    out('# Description: ' || l_descr);
    out('# ==================================================================');
    out('#');

    /*    out('#********* Getting the apps pwd ****************');
        out('pwd=`sqlplus -s $FCP_LOGIN<< EOF ');
        out('  set echo off');
        out('  set flush off');
        out('  set serveroutput off');
        out('  set termout off');
        out('  set newpage 0');
        out('  set space 0');
        out('  set pagesize 0');
        out('  set feedback off');
        out('  set heading off');
        out('  set trimspool on');
        out('  set tab off');
        out('  set linesize 5000');
        out('  set long 1000000');
        out('  set verify off');
        out('  set define off');
        out('select substr(''$FCP_LOGIN'',instr(''$FCP_LOGIN'',''/'')+1)');
        out('from dual;');
        out('EXIT;');
        out('EOF`');
        out('');
        out('echo ''pwd:''$pwd    ');
        out('    ');
    */
    /* out();
     out('#==  Create main folder directory ==');
     out(' if [ ! -d "' || g_fold_name || '" ];then');
     out(' mkdir "' || g_fold_name || '"');
     out(' fi');
     out(' cd ' || g_fold_name);
     out();



     -- to create only the needed subfolders
     FOR l2_types_rec IN c_all_l2_types LOOP
       out(' if [ ! -d "' || l2_types_rec.TYPE || '" ]; then');
       out('  mkdir ' || l2_types_rec.TYPE || ' ');
       out(' fi ');
       out();
     END LOOP;
*/

    out('  if [ "x"${AME_TOP} = "x" ];then ');
    out('  AME_TOP=$PER_TOP ');
    out(' fi');
    out(' if [ ! -d "LOGS_DOWN" ]; then');
    out('  mkdir LOGS_DOWN ');
    out(' fi ');
    out();
    out(' if [ ! -d "LOGS_UP" ]; then');
    out('  mkdir LOGS_UP ');
    out(' fi ');
    out();
    out(' if [ ! -d "DOC" ]; then');
    out('  mkdir DOC ');
    out(' fi ');
    out();
    out(' if [ ! -d "IMP" ]; then');
    out('  mkdir IMP ');
    out(' fi ');
    out();
    out();

    out();
    out();

    out(' ARGV="$@"');
    out();
    out(' pwd=$1');
    out();
    --    out(' cust_top=$2');

    out();
    out(' if [ "x"$ARGV = "x" ] ; then');
    out('  echo "Error: Missing parameters!! "');
    out('  echo Enter apps password!');
    out('  stty -echo');
    out('  read apps_pwd');
    out('  pwd=$apps_pwd');
    out('  stty echo');
    out('  #echo "usage:  sh script_name apps_passwd"');
    out(' fi');
    out();
    out();

    --    out(' dbserver=' || NVL(l_uplo_server, fnd_profile.VALUE('GL_JAHE_DB_HOST')));
    --    out(' dbport=' || NVL(l_uplo_port, fnd_profile.VALUE('CSF_MAP_DB_PORT')));
    --    out(' dbsid=' || NVL(l_uplo_sid, fnd_profile.VALUE('CSF_MAP_DB_SID')));
    out(' dbserver=$2');
    out(' dbport=$3');
    out(' dbsid=$4');

    out();
    out(' # Check Oracle connection. Is user/$pwd valid?');
    out(' exit | sqlplus -s apps/$pwd | grep denied > /dev/null');
    --    out(' exit | sqlplus -s $FCP_LOGIN | grep denied > /dev/null');

    out();
    out(' if [ $? -eq 1 ];then');
    out();
    out();

    FOR l2_types_rec IN c_all_l2_types LOOP
      out();
      out('##');
      out('## =================  ### ' || l2_types_rec.TYPE || ' ### ================= ##');
      out('##');
      out();

      FOR all_types_rec IN c_all_types(l2_types_rec.TYPE) LOOP
        out('## ------------- ' || all_types_rec.txt_path || ' ------------------');
        out();

        FOR all_files_rec IN c_all_files(all_types_rec.TYPE) LOOP
          my_cmd := fun_get_file_cmd('UP', p_folder_id, all_files_rec.file_id);

          out(my_cmd);
        END LOOP;

        out();
      END LOOP;
    END LOOP;

    out(' mv *.log LOGS_UP/');
    out(' cat LOGS_UP/* > LOGS_UP/ALL_LOGS_UP.log');
    out(' else');
    out(' echo "Invalid username/password! Try again. "');
    out(' fi');
  END proc_write_up_sh;

  --================================================================================--
  --   NAME     : fun_search
  --   PURPOSE  : fun_search
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  FUNCTION fun_search(
    p_type                   IN VARCHAR2,
    p_file_name              IN VARCHAR2 DEFAULT NULL,
    p_attribute1             IN VARCHAR2 DEFAULT NULL,
    p_last_updated_by        IN VARCHAR2 DEFAULT NULL,
    p_last_update_date_from  IN DATE DEFAULT NULL,
    p_last_update_date_to    IN DATE DEFAULT NULL)
    RETURN xxgr_exp_search_tbl
    PIPELINED
    PARALLEL_ENABLE
  IS
    out_rec             xxgr_exp_search_obj
                          := xxgr_exp_search_obj(
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL); -- Object Type
    v_dyn_sql           VARCHAR2(32000) := NULL;
    v_core_sql          VARCHAR2(32000) := NULL;
    v_user_id           NUMBER(15);
    v_core_view         VARCHAR2(100);
    v_core_dates VARCHAR2(32000) := NULL;
    v_core_order VARCHAR2(32000) := NULL;

    TYPE ref_cur IS REF CURSOR;

    incs_cur            ref_cur;
    num                 NUMBER(20);
    --    i                   NUMBER(20) := 1;

    v_type              VARCHAR2(100);
    v_file_name         VARCHAR2(100);
    v_file_desc         VARCHAR2(500);
    v_attribute1        VARCHAR2(500);
    v_attribute2        VARCHAR2(500);
    v_attribute3        VARCHAR2(500);
    v_attribute4        VARCHAR2(500);
    v_attribute5        VARCHAR2(500);
    v_attribute6        VARCHAR2(500);
    v_attribute7        VARCHAR2(500);
    v_attribute8        VARCHAR2(500);
    v_attribute9        VARCHAR2(500);
    v_attribute10       VARCHAR2(500);
    v_last_updated_by   VARCHAR2(100);
    v_last_update_date  DATE;

    l_lf_pos            NUMBER := 0;
    l_prev_lf_pos       NUMBER := 0;
    l_len               NUMBER;
    l_line              VARCHAR2(2000);

    l_ver               VARCHAR2(10);


  BEGIN
    SELECT version INTO l_ver FROM v$instance;

    IF l_ver LIKE '9.2%' THEN
      DBMS_OUTPUT.put_line(
        'DISABLE ALTER SESSION DUE TO ORA-14552: Cannot Perform a DDL Commit or Rollback Inside a Query or DML ');
    ELSE
      EXECUTE IMMEDIATE 'alter session set NLS_NUMERIC_CHARACTERS=''.,''';

      EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT = ''dd/mm/rrrr''';
    END IF;


    -- v_core_dates
    IF p_last_update_date_from IS NOT NULL AND p_last_update_date_to IS NOT NULL THEN
        v_core_dates :=
             '
and t1.last_update_date between to_date('''
          || TO_CHAR(p_last_update_date_from)
          || ' 00:00:00'', ''dd/mm/yyyy hh24:mi:ss'') and to_date('''
          || TO_CHAR(p_last_update_date_to)
          || ' 23:59:00'', ''dd/mm/yyyy hh24:mi:ss'') ';
      ELSIF p_last_update_date_from IS NOT NULL AND p_last_update_date_to IS NULL THEN
        v_core_dates :=
             '
and t1.last_update_date >= to_date('''
          || TO_CHAR(p_last_update_date_from)
          || ' 00:00:00'', ''dd/mm/yyyy hh24:mi:ss'') ';
      ELSIF p_last_update_date_from IS NULL AND p_last_update_date_to IS NOT NULL THEN
        v_core_dates :=
             '
and t1.last_update_date <= to_date('''
          || TO_CHAR(p_last_update_date_to)
          || ' 23:59:00'', ''dd/mm/yyyy hh24:mi:ss'') ';
      END IF;


    -- v_core_order
    v_core_order := '
order by  1,2,3,4,5,6,7,8,9,10';


    --***********************************************************************--
    -- AMEACTTYPC
    --***********************************************************************--
    IF p_type = 'AMEACTTYPC' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- AMEAPGR
    --***********************************************************************--
    ELSIF p_type = 'AMEAPGR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- AMEAPGRC
    --***********************************************************************--
    ELSIF p_type = 'AMEAPGRC' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- AMEATTRU
    --***********************************************************************--
    ELSIF p_type = 'AMEATTRU' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- AMECOND
    --***********************************************************************--
    ELSIF p_type = 'AMECOND' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- AMERUL
    --***********************************************************************--
    ELSIF p_type = 'AMERUL' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- AMERULACT
    --***********************************************************************--
    ELSIF p_type = 'AMERULACT' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- ALR
    --***********************************************************************--
    ELSIF p_type = 'ALR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- ALRD
    --***********************************************************************--
    ELSIF p_type = 'ALRD' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- APP
    --***********************************************************************--
    ELSIF p_type = 'APP' THEN
      v_core_view := 'fnd_application_vl';

      v_core_sql :=
           'select '''
        || p_type
        || ''',
    t1.application_short_name,
    t1.application_name,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    u.user_name,
    t1.last_update_date
from '
        || v_core_view
        || ' t1, fnd_user u ';
      v_core_sql := v_core_sql || '
where t1.last_updated_by = u.user_id ';

      IF p_file_name IS NOT NULL THEN
        v_core_sql := v_core_sql || '
and t1.application_short_name like ''' || p_file_name ||'%''';
      END IF;

      IF p_last_updated_by IS NOT NULL THEN
        v_core_sql := v_core_sql || '
and u.user_name like ''' || p_last_updated_by ||'%''';
      END IF;

      v_core_sql := v_core_sql || '  '||v_core_dates;
      v_core_sql := v_core_sql || '
             ' || v_core_order;

    --***********************************************************************--
    -- AUD
    --***********************************************************************--
    ELSIF p_type = 'AUD' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- CONCPR
    --***********************************************************************--
    ELSIF p_type = 'CONCPR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- CONCEX
    --***********************************************************************--
    ELSIF p_type = 'CONCEX' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- CONCMGR
    --***********************************************************************--
    ELSIF p_type = 'CONCMGR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- DFF
    --***********************************************************************--
    ELSIF p_type = 'DFF' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- DFFCONT
    --***********************************************************************--
    ELSIF p_type = 'DFFCONT' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- FNDFOLD
    --***********************************************************************--
    ELSIF p_type = 'FNDFOLD' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- FNDLOB
    --***********************************************************************--
    ELSIF p_type = 'FNDLOB' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- FNDSCHED
    --***********************************************************************--
    ELSIF p_type = 'FNDSCHED' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- FORM
    --***********************************************************************--
    ELSIF p_type = 'FORM' THEN
      v_core_view := 'fnd_form_vl';

      v_core_sql :=
           'select '''
        || p_type
        || ''',
    t1.form_name,
    t1.user_form_name,
    a.application_short_name,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    u.user_name,
    t1.last_update_date
from '
        || v_core_view
        || ' t1, fnd_user u, fnd_application a ';
      v_core_sql := v_core_sql || '
where t1.last_updated_by = u.user_id
    and t1.application_id = a.application_id';

      IF p_file_name IS NOT NULL THEN
        v_core_sql := v_core_sql || '
    and t1.form_name like ''' || p_file_name || '%''';
      END IF;

      IF p_attribute1 IS NOT NULL THEN
        v_core_sql :=
          v_core_sql || '
    and a.application_short_name like ''' || p_attribute1 || '%''';
      END IF;

      IF p_last_updated_by IS NOT NULL THEN
        v_core_sql :=
          v_core_sql || '
    and u.user_name like ''' || p_last_updated_by || '%''';
      END IF;

      v_core_sql := v_core_sql || '  '||v_core_dates;
      v_core_sql := v_core_sql || '
             ' || v_core_order;



    --***********************************************************************--
    -- FUNC
    --***********************************************************************--
    ELSIF p_type = 'FUNC' THEN
      v_core_view := 'fnd_form_functions_vl';

      v_core_sql :=
           'select '''
        || p_type
        || ''',
    t1.function_name,
    t1.user_function_name,
    a.application_short_name,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    u.user_name,
    t1.last_update_date
from '
        || v_core_view
        || ' t1, fnd_user u, fnd_application a ';
      v_core_sql := v_core_sql || '
where t1.last_updated_by = u.user_id
    and t1.application_id = a.application_id';

      IF p_file_name IS NOT NULL THEN
        v_core_sql := v_core_sql || '
    and t1.function_name like ''' || p_file_name || '%''';
      END IF;

      IF p_attribute1 IS NOT NULL THEN
        v_core_sql :=
          v_core_sql || '
    and a.application_short_name like ''' || p_attribute1 || '%''';
      END IF;

      IF p_last_updated_by IS NOT NULL THEN
        v_core_sql :=
          v_core_sql || '
    and u.user_name like ''' || p_last_updated_by || '%''';
      END IF;

      v_core_sql := v_core_sql || '  '||v_core_dates;
      v_core_sql := v_core_sql || '
             ' || v_core_order;

    --***********************************************************************--
    -- PERS
    --***********************************************************************--
    ELSIF p_type = 'PERS' THEN
      v_core_view := 'fnd_form_custom_rules';

      v_core_sql :=
           'select '''
        || p_type
        || ''',
    t1.form_name,
    t1.description,
    null,
    t1.function_name,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    u.user_name,
    t1.last_update_date
from '
        || v_core_view
        || ' t1, fnd_user u ';
      v_core_sql := v_core_sql || '
where t1.last_updated_by = u.user_id';

      IF p_file_name IS NOT NULL THEN
        v_core_sql := v_core_sql || '
    and t1.form_name like ''' || p_file_name || '%''';
      END IF;

      IF p_last_updated_by IS NOT NULL THEN
        v_core_sql :=
          v_core_sql || '
    and u.user_name like ''' || p_last_updated_by || '%''';
      END IF;

      v_core_sql := v_core_sql || '  '||v_core_dates;
      v_core_sql := v_core_sql || '
             ' || v_core_order;

    --***********************************************************************--
    -- HRMSTASK
    --***********************************************************************--
    ELSIF p_type = 'HRMSTASK' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- HRMSNAV
    --***********************************************************************--
    ELSIF p_type = 'HRMSNAV' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- KFF
    --***********************************************************************--
    ELSIF p_type = 'KFF' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- LOOK
    --***********************************************************************--
    ELSIF p_type = 'LOOK' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- MENU
    --***********************************************************************--
    ELSIF p_type = 'MENU' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- SUBMENU
    --***********************************************************************--
    ELSIF p_type = 'SUBMENU' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- MSG
    --***********************************************************************--
    ELSIF p_type = 'MSG' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PRINT
    --***********************************************************************--
    ELSIF p_type = 'PRINT' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PRINTST
    --***********************************************************************--
    ELSIF p_type = 'PRINTST' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PRINTDR
    --***********************************************************************--
    ELSIF p_type = 'PRINTDR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PRINTTY
    --***********************************************************************--
    ELSIF p_type = 'PRINTTY' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PROF
    --***********************************************************************--
    ELSIF p_type = 'PROF' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PROF_NO_VAL
    --***********************************************************************--
    ELSIF p_type = 'PROF_NO_VAL' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PROF_RESP
    --***********************************************************************--
    ELSIF p_type = 'PROF_RESP' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- RG
    --***********************************************************************--
    ELSIF p_type = 'RG' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- RGUNPR
    --***********************************************************************--
    ELSIF p_type = 'RGUNPR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- RGUNSET
    --***********************************************************************--
    ELSIF p_type = 'RGUNSET' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- RS
    --***********************************************************************--
    ELSIF p_type = 'RS' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- RSLINK
    --***********************************************************************--
    ELSIF p_type = 'RSLINK' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- RESP
    --***********************************************************************--
    ELSIF p_type = 'RESP' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- USR
    --***********************************************************************--
    ELSIF p_type = 'USR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- VS
    --***********************************************************************--
    ELSIF p_type = 'VS' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- VSVAL
    --***********************************************************************--
    ELSIF p_type = 'VSVAL' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- VSVAL1157
    --***********************************************************************--
    ELSIF p_type = 'VSVAL1157' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- ADIINT
    --***********************************************************************--
    ELSIF p_type = 'ADIINT' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- ADILAY
    --***********************************************************************--
    ELSIF p_type = 'ADILAY' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- ADIMAP
    --***********************************************************************--
    ELSIF p_type = 'ADIMAP' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- ADICONT
    --***********************************************************************--
    ELSIF p_type = 'ADICONT' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- WFROLES
    --***********************************************************************--
    ELSIF p_type = 'WFROLES' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- WF
    --***********************************************************************--
    ELSIF p_type = 'WF' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- XMLTEM
    --***********************************************************************--
    ELSIF p_type = 'XMLTEM' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- FO
    --***********************************************************************--
    ELSIF p_type = 'FO' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- RTF
    --***********************************************************************--
    ELSIF p_type = 'RTF' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- XML
    --***********************************************************************--
    ELSIF p_type = 'XML' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- XLIFF
    --***********************************************************************--
    ELSIF p_type = 'XLIFF' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- FMB
    --***********************************************************************--
    ELSIF p_type = 'FMB' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- FMB12
    --***********************************************************************--
    ELSIF p_type = 'FMB12' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- GIF
    --***********************************************************************--
    ELSIF p_type = 'GIF' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PLL
    --***********************************************************************--
    ELSIF p_type = 'PLL' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PLL12
    --***********************************************************************--
    ELSIF p_type = 'PLL12' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- RDF
    --***********************************************************************--
    ELSIF p_type = 'RDF' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- INSSCR
    --***********************************************************************--
    ELSIF p_type = 'INSSCR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- SCRIPT
    --***********************************************************************--
    ELSIF p_type = 'SCRIPT' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- DBLINK
    --***********************************************************************--
    ELSIF p_type = 'DBLINK' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- DIR
    --***********************************************************************--
    ELSIF p_type = 'DIR' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- FUN
    --***********************************************************************--
    ELSIF p_type = 'FUN' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- IND
    --***********************************************************************--
    ELSIF p_type = 'IND' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- JAVA
    --***********************************************************************--
    ELSIF p_type = 'JAVA' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- MATV
    --***********************************************************************--
    ELSIF p_type = 'MATV' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PKG
    --***********************************************************************--
    ELSIF p_type = 'PKG' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PKGSP
    --***********************************************************************--
    ELSIF p_type = 'PKGSP' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PKGBODY
    --***********************************************************************--
    ELSIF p_type = 'PKGBODY' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- PROC
    --***********************************************************************--
    ELSIF p_type = 'PROC' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- SEQ
    --***********************************************************************--
    ELSIF p_type = 'SEQ' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- SYN
    --***********************************************************************--
    ELSIF p_type = 'SYN' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- TBL
    --***********************************************************************--
    ELSIF p_type = 'TBL' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- TRG
    --***********************************************************************--
    ELSIF p_type = 'TRG' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- TYPE
    --***********************************************************************--
    ELSIF p_type = 'TYPE' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- VIEW
    --***********************************************************************--
    ELSIF p_type = 'VIEW' THEN
      PIPE ROW (out_rec);
      RETURN;
    --***********************************************************************--
    -- ELSE TYPE
    --***********************************************************************--
    ELSE
      PIPE ROW (out_rec);
      RETURN;
    END IF;

    -- Full Text of the SQL Statement (to be executed)
    v_dyn_sql := v_core_sql || v_dyn_sql || '
    ';

    /*    SELECT CEIL(LENGTH(REPLACE(REPLACE(REPLACE(v_dyn_sql, CHR(10)), CHR(13)), '  ', ' ')) / 255)
        INTO   num
        FROM   DUAL;

        FOR sel IN i .. num LOOP

          IF i = 1 THEN
            DBMS_OUTPUT.put_line(SUBSTR(v_dyn_sql, 1, 255));
          ELSE
            DBMS_OUTPUT.put_line(SUBSTR(v_dyn_sql, (255 * (i-1))+1, 255));
          END IF;
          i := i + 1;
        END LOOP;
    */

    LOOP
      l_len := LENGTH(v_dyn_sql);

      l_lf_pos :=
        INSTR(
          v_dyn_sql,
          CHR(10),
          l_lf_pos + 1,
          1);

      l_line := SUBSTR(v_dyn_sql, l_prev_lf_pos + 1, l_lf_pos - l_prev_lf_pos);

      DBMS_OUTPUT.put_line(l_line);

      l_prev_lf_pos := l_lf_pos;
      EXIT WHEN l_lf_pos = 0;
    END LOOP;

    --dbms_output.put_line('@@');
    --DBMS_OUTPUT.put_line(v_dyn_sql);

    OPEN incs_cur FOR v_dyn_sql;

    LOOP
      FETCH incs_cur
      INTO v_type,
           v_file_name,
           v_file_desc,
           v_attribute1,
           v_attribute2,
           v_attribute3,
           v_attribute4,
           v_attribute5,
           v_attribute6,
           v_attribute7,
           v_attribute8,
           v_attribute9,
           v_attribute10,
           v_last_updated_by,
           v_last_update_date;

      EXIT WHEN incs_cur%NOTFOUND;

      out_rec.TYPE := v_type;
      out_rec.file_name := v_file_name;
      out_rec.file_desc := v_file_desc;
      out_rec.attribute1 := v_attribute1;
      out_rec.attribute2 := v_attribute2;
      out_rec.attribute3 := v_attribute3;
      out_rec.attribute4 := v_attribute4;
      out_rec.attribute5 := v_attribute5;
      out_rec.attribute6 := v_attribute6;
      out_rec.attribute7 := v_attribute7;
      out_rec.attribute8 := v_attribute8;
      out_rec.attribute9 := v_attribute9;
      out_rec.attribute10 := v_attribute10;
      out_rec.last_updated_by := v_last_updated_by;
      out_rec.last_update_date := v_last_update_date;

      PIPE ROW (out_rec);
    END LOOP;

    CLOSE incs_cur;

    RETURN;
  EXCEPTION
    WHEN OTHERS THEN
      PIPE ROW (out_rec);
      RETURN;
  END fun_search;
/*
  --================================================================================--
  --   NAME     : proc_run_request
  --   PURPOSE  : proc_run_request
  --
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  PROCEDURE proc_run_request(
    x_status                       OUT VARCHAR2,
    x_message                      OUT VARCHAR2,
    x_request_id                   OUT NUMBER,
    p_conc_request_application  IN     VARCHAR2,
    p_conc_request_name         IN     VARCHAR2,
    p_description               IN     VARCHAR2,
    p_args                      IN     trec_args)
  IS
    l_success           BOOLEAN;
    l_request_id        NUMBER;
    l_message           VARCHAR2(2000);
    exc_run_request     EXCEPTION;
    l_running_requests  NUMBER;
  BEGIN
    x_status := 'S';
    x_message := NULL;

    /*    IF fnd_global.user_id = -1 THEN
          fnd_global.apps_initialize(30142, 50007, 7002);
        END IF;*|

    /*    l_success := fnd_request.set_options(implicit => 'WARNING');

        IF (l_success = FALSE) THEN
          l_message := 'Could not set Language options.';
          RAISE exc_run_request;
        END IF;
    *|
    l_request_id :=
      fnd_request.submit_request(
        application  => p_conc_request_application,
        program      => p_conc_request_name,
        description  => p_description,
        start_time   => NULL,
        sub_request  => NULL,
        argument1    => p_args.arg01 /*,
         argument2    => p_args.arg02,
         argument3    => p_args.arg03*|
                                    );

    COMMIT;
    x_request_id := l_request_id;

    --Wait for all requests to finish.
    LOOP
      SELECT COUNT(9)
      INTO   l_running_requests
      FROM   fnd_concurrent_requests fcr
      WHERE  fcr.request_id = l_request_id AND phase_code <> 'C';

      EXIT WHEN l_running_requests <= 0;

      --wait for 5 seconds till check again.
      DBMS_LOCK.sleep(5);
    END LOOP;

    IF (l_request_id = -1) THEN
      x_request_id := NULL;
      l_message := 'Could not start request.';
      RAISE exc_run_request;
    ELSE
      x_request_id := l_request_id;
    END IF;
  EXCEPTION
    WHEN exc_run_request THEN
      x_status := 'E';
      x_message := l_message;
  END proc_run_request;
*/
-------------------------------------------------------------------------------------

/*
  --================================================================================--
  --   NAME     : proc_run_request_and_move
  --   PURPOSE  : proc_run_request_and_move
  --
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  PROCEDURE proc_run_request_and_move(
    x_status                       OUT VARCHAR2,
    x_message                      OUT VARCHAR2,
    x_request_id                   OUT NUMBER,
    p_folder_id                 IN     NUMBER,
    p_conc_request_application  IN     VARCHAR2,
    p_conc_request_name         IN     VARCHAR2,
    p_description               IN     VARCHAR2
                                               )
  IS
    l_success                  BOOLEAN;
    l_request_id               NUMBER;
    l_message                  VARCHAR2(2000);
    exc_run_request            EXCEPTION;

    p_args                     trec_args;

    l_run_request_id           NUMBER;

    c_procedure_name  CONSTANT VARCHAR2(30) := 'proc_run_request_and_move';
    l_stage                    VARCHAR2(100);

    l_outfile_name             VARCHAR2(2000);
    l_uplo_request_id          NUMBER;
    l_running_requests         NUMBER;
    l_fnd_lobs_file_id         NUMBER;
    gfm_agent                  VARCHAR2(255);
    l_out_folder_name          VARCHAR2(2000);
  BEGIN
    EXECUTE IMMEDIATE 'alter session set NLS_NUMERIC_CHARACTERS=''.,''';

    EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT = ''dd/mm/yyyy''';

    g_url := '';
    x_status := 'S';
    x_message := NULL;

    l_stage := 'START';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    --Parameters
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_folder_id [' || p_folder_id || ']');

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_conc_request_application [' || p_conc_request_application || ']');

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_conc_request_name [' || p_conc_request_name || ']');

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_description [' || p_description || ']');

    IF fnd_global.user_id = -1 THEN
      fnd_global.apps_initialize(30142, 50007, 7002);
    END IF;

    -- 1 *********************************************************************--
    l_stage := '#1 Run concurrent to get sh script';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    l_success := fnd_request.set_options(implicit => 'WARNING');

    IF (l_success = FALSE) THEN
      l_message := 'Could not set Language options.';
      RAISE exc_run_request;
    END IF;

    p_args.arg01 := TO_CHAR(p_folder_id);
    p_args.arg02 := NULL;
    p_args.arg03 := NULL;
    p_args.arg04 := NULL;
    p_args.arg05 := NULL;
    p_args.arg06 := NULL;
    p_args.arg07 := NULL;
    p_args.arg08 := NULL;
    p_args.arg09 := NULL;
    p_args.arg10 := NULL;

    -- Call the proc_run_request
    proc_run_request(
      x_status                    => x_status,
      x_message                   => x_message,
      x_request_id                => l_run_request_id,
      p_conc_request_application  => 'XXGR',
      p_conc_request_name         => 'XXGR_EXP_FOLDER_DOWN_SH',
      p_description               => 'EXP DOWN SH',
      p_args                      => p_args);

    COMMIT;

    IF (l_request_id = -1) THEN
      x_request_id := NULL;
      l_message := 'Could not start request.';
      RAISE exc_run_request;
    ELSE
      x_request_id := l_run_request_id;
    END IF;

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'l_run_request_id [' || l_run_request_id || ']');

    -- 2 *********************************************************************--
    l_stage := '#2 Upload to fnd_lobs';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    SELECT req.outfile_name
    INTO   l_outfile_name
    FROM   fnd_concurrent_requests req
    WHERE  req.request_id = l_run_request_id;

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'l_outfile_name [' || l_outfile_name || ']');

    --UPLOAD
    l_uplo_request_id :=
      fnd_request.submit_request(
        application  => 'FND',
        program      => 'FNDGFU',
        description  => NULL,
        start_time   => NULL,
        sub_request  => FALSE,
        argument1    => 'PROGRAM_NAME=XXGR_EXP_PKG',
        argument2    => 'PROGRAM_TAG=' || l_run_request_id,
        argument3    => 'LANGUAGE=EL',
        argument4    => 'CONTENT_TYPE=' || 'text/plain',
        argument5    => l_outfile_name);

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'l_uplo_request_id [' || l_uplo_request_id || ']');
    COMMIT;

    --Wait for all requests to finish.
    LOOP
      SELECT COUNT(9)
      INTO   l_running_requests
      FROM   fnd_concurrent_requests fcr
      WHERE  fcr.request_id = l_uplo_request_id AND phase_code <> 'C';

      EXIT WHEN l_running_requests <= 0;

      --wait for 5 seconds till check again.
      DBMS_LOCK.sleep(5);
    END LOOP;

    BEGIN
      SELECT file_id
      INTO   l_fnd_lobs_file_id
      FROM   fnd_lobs l
      WHERE  program_name = 'XXGR_EXP_PKG' AND program_tag = l_run_request_id;

      log_message(
        p_package_name   => g_package_name,
        p_function_call  => c_procedure_name,
        p_stage          => l_stage,
        p_message_text   => 'l_fnd_lobs_file_id [' || l_fnd_lobs_file_id || ']');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_message := 'No file_id found';
        RAISE exc_run_request;
    END;

    SELECT out_folder_name
    INTO   l_out_folder_name
    FROM   xxgr_exp_folder
    WHERE  folder_id = p_folder_id;

    UPDATE fnd_lobs
    SET    file_name = l_out_folder_name || '_down.prog'
    WHERE  file_id = l_fnd_lobs_file_id;

    COMMIT;

    gfm_agent := fnd_web_config.gfm_agent;

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'gfm_agent [' || gfm_agent || ']');

    g_url := fnd_gfm.construct_download_url(gfm_agent, l_fnd_lobs_file_id, FALSE);

    UPDATE xxgr_exp_folder
    SET    status = 'EXP',
           req_id = l_run_request_id,
           attribute1 = l_fnd_lobs_file_id,
           attribute2 = g_url
    WHERE  folder_id = p_folder_id;

    --      fnd_utilities.open_url(l_url);
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'g_url [' || g_url || ']');

    /*
    -- 3 *********************************************************************--
        l_stage := '#3 Run HOST concurrent to run sh script';
        log_message(
          p_package_name   => g_package_name,
          p_function_call  => c_procedure_name,
          p_stage          => l_stage);

        l_success := fnd_request.set_options(implicit => 'WARNING');

        IF (l_success = FALSE) THEN
          l_message := 'Could not set Language options.';
          RAISE exc_run_request;
        END IF;

        p_args.arg01 := NULL;-- TO_CHAR(p_folder_id);
        p_args.arg02 := NULL;
        p_args.arg03 := NULL;
        p_args.arg04 := NULL;
        p_args.arg05 := NULL;
        p_args.arg06 := NULL;
        p_args.arg07 := NULL;
        p_args.arg08 := NULL;
        p_args.arg09 := NULL;
        p_args.arg10 := NULL;

        -- Call the proc_run_request
        proc_run_request(
          x_status                    => x_status,
          x_message                   => x_message,
          x_request_id                => l_run_request_id,
          p_conc_request_application  => 'XXGR',
          p_conc_request_name         => 'XXGR_EXP_HOST',
          p_description               => 'EXP DOWN HOST SH',
          p_args                      => p_args);

        COMMIT;

        IF (l_request_id = -1) THEN
          x_request_id := NULL;
          l_message := 'Could not start request.';
          RAISE exc_run_request;
        ELSE
          x_request_id := l_run_request_id;
        END IF;

        log_message(
          p_package_name   => g_package_name,
          p_function_call  => c_procedure_name,
          p_stage          => l_stage,
          p_message_text   => 'l_run_request_id [' || l_run_request_id || ']');

    *|

    -- END *********************************************************************--
    l_stage := 'END';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);
  EXCEPTION
    WHEN exc_run_request THEN
      x_status := 'E';
      x_message := l_message;
  END proc_run_request_and_move;
*/
-------------------------------------------------------------------------------------

/*
  --================================================================================--
  --   NAME     : proc_run_xmlp_request
  --   PURPOSE  : proc_run_xmlp_request
  --
  --
  --   REVISIONS:
  --   Ver        Date        Author            Description
  --   ---------  ----------  ---------------   ------------------------------------
  --   1.0        01-Jan-2013 Kyrkou Sofia      Initial version
  --
  --   NOTES    :
  --================================================================================--
  PROCEDURE proc_run_xmlp_request(
    x_status                       OUT VARCHAR2,
    x_message                      OUT VARCHAR2,
    x_request_id                   OUT NUMBER,
    p_request_language          IN     VARCHAR2,
    p_request_terittory         IN     VARCHAR2,
    p_request_nls_nun_chars     IN     VARCHAR2,
    p_template_language         IN     VARCHAR2,
    p_template_terittory        IN     VARCHAR2,
    p_printer                   IN     VARCHAR2,
    p_style                     IN     VARCHAR2,
    p_copies                    IN     NUMBER DEFAULT 0,
    p_conc_request_application  IN     VARCHAR2,
    p_conc_request_name         IN     VARCHAR2,
    p_conc_template_name        IN     VARCHAR2,
    p_description               IN     VARCHAR2,
    p_args                      IN     trec_args,
    p_output_format             IN     VARCHAR2 DEFAULT 'RTF')
  IS
    l_success        BOOLEAN;
    l_request_id     NUMBER;
    l_message        VARCHAR2(2000);
    exc_run_request  EXCEPTION;
  BEGIN
    x_status := 'S';
    x_message := NULL;

    l_success :=
      fnd_request.set_options(
        language            => p_request_language,
        territory           => p_request_terittory,
        numeric_characters  => p_request_nls_nun_chars);

    IF (l_success = FALSE) THEN
      l_message := 'Could not set Language options.';
      RAISE exc_run_request;
    END IF;

    l_success :=
      fnd_request.add_layout(
        template_appl_name  => p_conc_request_application,
        template_code       => p_conc_template_name,
        template_language   => p_template_language,
        template_territory  => p_template_terittory,
        output_format       => p_output_format);

    IF (l_success = FALSE) THEN
      l_message := 'Could not set layout parameters for Layout.';
      RAISE exc_run_request;
    END IF;

    l_success :=
      fnd_request.set_print_options(
        printer         => p_printer,
        style           => p_style,
        copies          => p_copies,
        save_output     => NULL,
        print_together  => NULL);

    IF (l_success = FALSE) THEN
      l_message := 'Could not set Print Options.';
      RAISE exc_run_request;
    END IF;

    l_request_id :=
      fnd_request.submit_request(
        application  => p_conc_request_application,
        program      => p_conc_request_name,
        description  => p_description,
        start_time   => NULL,
        sub_request  => NULL,
        argument1    => p_args.arg01,
        argument2    => p_args.arg02,
        argument3    => p_args.arg03);

    IF (l_request_id = -1) THEN
      x_request_id := NULL;
      l_message := 'Could not start request.';
      RAISE exc_run_request;
    ELSE
      x_request_id := l_request_id;
    END IF;
  EXCEPTION
    WHEN exc_run_request THEN
      x_status := 'E';
      x_message := l_message;
  END proc_run_xmlp_request;
*/
-------------------------------------------------------------------------------------

/*
  PROCEDURE send_email(
    x_error_message     OUT VARCHAR2,
    x_return_code       OUT VARCHAR2,
    p_po_num         IN     VARCHAR2,
    p_release_num    IN     VARCHAR2,
    p_show_terms     IN     VARCHAR2,
    p_format_type    IN     VARCHAR2)
  IS
    c_procedure_name  CONSTANT VARCHAR2(30) := 'send_email';
    l_stage                    VARCHAR2(100);

    l_error_message            VARCHAR2(32767);
    l_return_code              VARCHAR2(100);

    exc_send_email             EXCEPTION;

    l_request_id               NUMBER := 0;

    l_check_request_id         NUMBER;
    l_wait                     BOOLEAN;
    l_phase                    VARCHAR2(50);
    l_status                   VARCHAR2(50);
    l_devphase                 VARCHAR2(50);
    l_devstatus                VARCHAR2(50);
    l_message                  VARCHAR2(50);

    len                        NUMBER := 280;
    l_lang                     VARCHAR2(50);
    l_email                    VARCHAR2(100);
    l_vendor_name              VARCHAR2(100);

    l_printer                  VARCHAR2(255);
    l_copies                   NUMBER := 0;
    l_template_language        VARCHAR2(10);
    l_template_territory       VARCHAR2(10);
    l_request_language         VARCHAR2(10);
    l_request_territory        VARCHAR2(10);
    l_request_nls_num_chars    VARCHAR2(10);
    l_xml_layout               BOOLEAN;

    l_request_descr            VARCHAR2(255);

    l_template_name            VARCHAR2(255);

    l_output_line              VARCHAR2(2000);
    l_running_requests         NUMBER;
    l_idx                      PLS_INTEGER;

    p_sent_files               apps.xxgr_po_commrep_pkg.sent_files_tbl;

    req_id                     fnd_concurrent_requests.request_id%TYPE;
    l_lob_filename             VARCHAR2(255);
    l_success                  BOOLEAN;
    l_file_id                  NUMBER;

    l_file                     UTL_FILE.file_type;
    l_buffer                   RAW(32767);
    l_amount                   BINARY_INTEGER := 32767;
    l_pos                      INTEGER := 1;
    l_blob                     BLOB;
    l_blob_len                 INTEGER;
    myfile_name                VARCHAR2(250);
  BEGIN
    EXECUTE IMMEDIATE 'alter session set NLS_NUMERIC_CHARACTERS=''.,''';

    EXECUTE IMMEDIATE 'alter session set NLS_DATE_FORMAT = ''dd/mm/yyyy''';

    l_stage := 'START';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    --Parameters
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_po_num [' || p_po_num || ']');

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_release_num [' || p_release_num || ']');

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_show_terms [' || p_show_terms || ']');

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'p_format_type [' || p_format_type || ']');

    out(RPAD('~', len, '~'));
    out('Parameters: ');
    out(RPAD('~', len, '~'));

    out(RPAD('PO Number: ', 40) || RPAD(p_po_num, 40));
    out(RPAD('Release Number: ', 40) || RPAD(p_release_num, 40));
    out(RPAD('Show Terms: ', 40) || RPAD(p_show_terms, 40));
    out(RPAD('Format Type: ', 40) || RPAD(p_format_type, 40));
    out('');

    -----------------------------------------------------------------
    -- get language
    -----------------------------------------------------------------

    -- 1 *********************************************************************--
    l_stage := '#1 Get language';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'l_lang [' || l_lang || ']');

    IF l_lang IS NULL THEN
      l_error_message := 'No language found!!';
      out(l_error_message);
      RAISE exc_send_email;
    END IF;

    -- 2 *********************************************************************--
    l_stage := '#2 Get email';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'l_email [' || l_email || ']');

    IF l_email IS NULL THEN
      l_error_message := 'No email found!!';
      out(l_error_message);
      RAISE exc_send_email;
    END IF;

    -- 3 *********************************************************************--
    l_stage := '#3 Get vendor name';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'l_vendor_name [' || l_vendor_name || ']');

    IF l_vendor_name IS NULL THEN
      l_error_message := 'No vendor name found!!';
      out(l_error_message);
      RAISE exc_send_email;
    END IF;

    -- 4 *********************************************************************--
    l_stage := '#4 Run Concurrent';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    l_printer := 'noprint'; --fnd_profile.VALUE('PRINTER');

    IF l_lang = 'EL' THEN
      l_template_language := 'el';
      l_template_territory := 'GR';
      l_request_language := 'GREEK';
      l_request_territory := 'GREECE';
      l_request_nls_num_chars := ',.';
    ELSE
      l_template_language := 'en';
      l_template_territory := 'US';
      l_request_language := 'AMERICAN';
      l_request_territory := 'AMERICA';
      l_request_nls_num_chars := '.,';
    END IF;

    IF (l_status = 'E') THEN
      l_output_line := 'Document with number ' || p_po_num || ' produced error ' || l_message;
    ELSE
      l_output_line :=
           'Document with number '
        || p_po_num
        || ' started concurrent request with ID '
        || l_request_id;
    END IF;

    out(l_output_line);

    COMMIT;

    --Wait for all requests to finish.
    LOOP
      SELECT COUNT(9)
      INTO   l_running_requests
      FROM   fnd_concurrent_requests fcr
      WHERE  fcr.request_id = l_request_id AND phase_code <> 'C';

      EXIT WHEN l_running_requests <= 0;

      --wait for 5 seconds till check again.
      DBMS_LOCK.sleep(5);
    END LOOP;

    -- 5 *********************************************************************--
    l_stage := '#5 Upload to fnd_lobs';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    l_lob_filename :=
      '/appl/otecomn/admin/out/OTE_appstst2/XXGR_PO_COMMREP_' || l_request_id || '_1.RTF';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'l_lob_filename [' || l_lob_filename || ']');

    l_success := fnd_request.set_options(implicit => 'WARNING');

    IF (l_success = FALSE) THEN
      l_message := 'Could not set options.';
      RAISE exc_send_email;
    END IF;

    --UPLOAD
    req_id :=
      fnd_request.submit_request(
        application  => 'FND',
        program      => 'FNDGFU',
        description  => NULL,
        start_time   => NULL,
        sub_request  => FALSE,
        argument1    => 'PROGRAM_NAME=SOFIA',
        argument2    => 'PROGRAM_TAG=SOF',
        argument3    => 'LANGUAGE=EL',
        argument4    => 'CONTENT_TYPE=application/msword',
        argument5    => l_lob_filename);
    --      argument5    => '@xxgr:sof/PO_REport.rtf');

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'req_id [' || req_id || ']');
    COMMIT;

    --Wait for all requests to finish.
    LOOP
      SELECT COUNT(9)
      INTO   l_running_requests
      FROM   fnd_concurrent_requests fcr
      WHERE  fcr.request_id = req_id AND phase_code <> 'C';

      EXIT WHEN l_running_requests <= 0;

      --wait for 5 seconds till check again.
      DBMS_LOCK.sleep(5);
    END LOOP;

    BEGIN
      SELECT MAX(file_id)
      INTO   l_file_id
      FROM   fnd_lobs l
      WHERE  file_name = l_lob_filename;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_message := 'No file_id found';
        RAISE exc_send_email;
    END;

    -- 6 *********************************************************************--
    l_stage := '#6 Export LOB to file';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    BEGIN
      -- Get LOB locator
      SELECT file_data, 'XXGR_PO_COMMREP_' || l_request_id || '_1.RTF'
      INTO   l_blob, myfile_name
      FROM   fnd_lobs l
      WHERE  file_id = l_file_id;

      l_blob_len := DBMS_LOB.getlength(l_blob);
      l_pos := 1;
      -- Open the destination file.
      l_file :=
        UTL_FILE.fopen(
          'XXGR_PO_ATTACHMENTS3',
          myfile_name,
          'w',
          32767);

      -- Read chunks of the BLOB and write them to the file
      -- until complete.
      WHILE l_pos < l_blob_len LOOP
        DBMS_LOB.read(
          l_blob,
          l_amount,
          l_pos,
          l_buffer);
        UTL_FILE.put_raw(l_file, l_buffer, TRUE);
        l_pos := l_pos + l_amount;
      END LOOP;

      -- Close the file.
      UTL_FILE.fclose(l_file);
    EXCEPTION
      WHEN OTHERS THEN
        -- Close the file if something goes wrong.
        IF UTL_FILE.is_open(l_file) THEN
          UTL_FILE.fclose(l_file);
        END IF;

        l_message := 'Read BLOB error';
        RAISE exc_send_email;
    END;

  --  |*
        l_success := fnd_request.set_options(implicit => 'WARNING');

        IF (l_success = FALSE) THEN
          l_message := 'Could not set options.';
          RAISE exc_send_email;
        END IF;

        --DOWNLOAD
        req_id :=
          fnd_request.submit_request(
            application  => 'FND',
            program      => 'FNDGFU',
            description  => NULL,
            start_time   => NULL,
            sub_request  => FALSE,
            argument1    => 'DOWNLOAD=' || l_file_id,
            argument2    => 'LINE_BREAKS=CRLF',
            argument3    => '/usr/tmp/XXGR_PO_COMMREP_' || l_request_id || '_1.RTF');

        log_message(
          p_package_name   => g_package_name,
          p_function_call  => c_procedure_name,
          p_stage          => l_stage,
          p_message_text   => 'req_id [' || req_id || ']');
        COMMIT;

        --Wait for all requests to finish.
        LOOP
          SELECT COUNT(9)
          INTO   l_running_requests
          FROM   fnd_concurrent_requests fcr
          WHERE  fcr.request_id = req_id AND phase_code <> 'C';

          EXIT WHEN l_running_requests <= 0;

          --wait for 5 seconds till check again.
          DBMS_LOCK.sleep(5);
        END LOOP;

   -- *|

    --wait for 60 seconds till check again.
    -- DBMS_LOCK.sleep(60);
    -- 10 *********************************************************************--
    l_stage := '#10 Send Email';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

   -- |*
        p_sent_files(1).filename := --'ttt.txt'; --'XXGR_PO_COMMREP_36487447_1.RTF';
        'XXGR_PO_COMMREP_' || l_request_id || '_1.RTF'; --'o' || l_request_id || '.tmp'; -- '.out';
        p_sent_files(1).current_path := '/usr/tmp'; --'/appl/otecomn/admin/out/OTE_appstst2';

        IF p_format_type = 'RTF' THEN
          p_sent_files(1).file_type := 'application/msword';
        ELSIF p_format_type = 'PDF' THEN
          p_sent_files(1).file_type := 'application/pdf';
        ELSIF p_format_type = 'EXCEL' THEN
          p_sent_files(1).file_type := 'application/vnd.ms-excel';
        ELSIF p_format_type = 'HTML' THEN
          p_sent_files(1).file_type := 'text/html';
        ELSE
          p_sent_files(1).file_type := 'text/plain';
        END IF;


        p_sent_files(1).send_file := TRUE;

        mail_files(
          x_return_message  => l_error_message,
          x_return_code     => l_return_code,
          p_address         => l_email,
          p_vendor_name     => l_vendor_name,
          p_sent_files      => p_sent_files,
          p_po_num          => p_po_num,
          p_release_num     => p_release_num);

        IF l_return_code = 'F' THEN
          RAISE exc_send_email;
        END IF;

  --  *|
    -- 11 *********************************************************************--
    -- Return out parameters
    l_stage := '#11 Return out parameters';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);

    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage,
      p_message_text   => 'OUT >> l_request_id [' || l_request_id || ']');

    -- END *********************************************************************--
    l_stage := 'END';
    log_message(
      p_package_name   => g_package_name,
      p_function_call  => c_procedure_name,
      p_stage          => l_stage);
  EXCEPTION
    WHEN exc_send_email THEN
      --ROLLBACK;

      out(l_error_message);
      x_error_message :=
        g_package_name || '.' || c_procedure_name || '@' || l_stage || ': ' || l_error_message;

      x_return_code := c_failed;
      log_message(
        p_package_name   => g_package_name,
        p_function_call  => c_procedure_name,
        p_stage          => l_stage,
        p_message_text   => x_error_message);
    WHEN OTHERS THEN
      --?         ROLLBACK;
      x_error_message :=
        g_package_name || '.' || c_procedure_name || '@' || l_stage || ': ' || l_error_message;
      x_return_code := c_failed;
      log_message(
        p_package_name   => g_package_name,
        p_function_call  => c_procedure_name,
        p_stage          => l_stage,
        p_message_text   => x_error_message);
  END send_email;

  */
END xxgr_exp_pkg;
/


-- End of DDL Script for Package Body APPS.XXGR_EXP_PKG

